package anton.admin.schema;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.sql.DataSource;

@ManagedBean(name = "shiftBean")
@SessionScoped
public final class ShiftBean implements Serializable {

    @Resource(name = "jdbc/antonsDatasource")
    private DataSource ds;
    List<Shift> shifts;

    public List<Shift> getShifts() {
        return shifts;
    }

    public void setShifts() throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet result = null;
        shifts.clear();

        try {
            if (ds == null) {
                throw new SQLException("Can't get data source");
            }

            con = ds.getConnection();

            if (con == null) {
                throw new SQLException("Can't get database connection");
            }

            ps = con.prepareStatement("SELECT s.shift_ID, s.shift_date, s.shift_time, e.emplyee_name, e.username FROM Shifts s, Employees e, EmployeeShifts es WHERE s.shift_ID=es.shift_ID AND es.username=e.username AND s.status != 'blue'");

            result = ps.executeQuery();

            while (result.next()) {
                Shift temp = new Shift();

                temp.setShift_ID(result.getInt("shift_ID"));
                temp.setShift_date(result.getString("shift_date"));
                temp.setShift_time(result.getString("shift_time"));
                temp.setEmp_name(result.getString("emplyee_name"));
                temp.setUsername(result.getString("username"));

                shifts.add(temp);
            }
        } finally {
            closeAll(result, ps, con);
        }
    }

    public void firstSetShifts() throws SQLException {
        if (shifts.isEmpty()) {
            setShifts();
        }
    }
    
    public void update(boolean updated) throws SQLException{
        if(updated)
        {
            setShifts();
        }
    }

    public ShiftBean() {
        shifts = new ArrayList<>();
    }

    private static void closeAll(ResultSet resultSet, PreparedStatement preState, Connection con) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
        }
        if (preState != null) {
            try {
                preState.close();
            } catch (SQLException e) {
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
            }
        }
    }

}
